package cz.acies.genea.data.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;

import cz.acies.data.DBConnector;
import cz.acies.data.dao.DefaultDAO;
import cz.acies.data.dao.DefaultMapListHandler;
import cz.acies.data.dao.DefaultRowProcessor;
import cz.acies.genea.data.persist.GedPerson;
import cz.acies.genea.data.persist.GedFamily;
import cz.acies.uniql.sql.InsertQuery;
import cz.acies.uniql.sql.SelectQuery;
import cz.acies.uniql.sql.UpdateQuery;
import cz.acies.uniql.sql.element.UniQLCondition;
import cz.acies.uniql.sql.element.UniQLWhere;
import cz.acies.utils.UIDGenerator;

public class GedFamilyDAO extends DefaultDAO
{
	public GedFamilyDAO() {
		this.tableName = "family";
	}
	
	public List<GedFamily> selectAll() {
		List<GedFamily> records =  new ArrayList<GedFamily>();
		try {  
			SelectQuery sql = new SelectQuery(dbmd()).addFrom("family");
			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			List<Map<String, Object>> list = qr.query(sql.toSQLString(), new DefaultMapListHandler(new DefaultRowProcessor()));
			for (Map<String, Object> param : list) {				
				String id = (String) param.get("ID");
				GedFamily record = new GedFamily(id, param);
				records.add(record);
			}
		} catch (Exception exc) {
			exc.printStackTrace();
		}
		return records;
	}

	public GedFamily selectById(String id) {
		GedFamily record = null;
		try {  
			//String sql = "SELECT * FROM families WHERE families.ID = '"+id+"'";
			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			List<Map<String, Object>> list = qr.query(new SelectQuery(dbmd())
					.addFrom("family").add(new UniQLCondition("family.id", "=", "'" + id + "'"))
				.toSQLString(), new DefaultMapListHandler(new DefaultRowProcessor()));
			for (Map<String, Object> param : list) {
				record = new GedFamily(id, param);
			}
		} catch (Exception exc) {
			exc.printStackTrace();
		}
		return record;
	}

	public String insert(GedFamily record) {
		String id = record.getId();
		try {
			if (id == null || id.trim().length() == 0 || !id.startsWith("GE.F.")) {
				id = UIDGenerator.createUID("GE.F");
				record.setId(id);
			}
			String manId = (record.hasMan()) ? 
				((record.getMan().hasId()) ? record.getMan().getId() : "") : "";
			String womanId = (record.hasWoman()) ? 
				((record.getWoman().hasId()) ? record.getWoman().getId() : "") : "";
				
			InsertQuery sql = new InsertQuery(dbmd()).into("family")
				.addParam("id", ""+record.getId())
				.addParam("projectId", ""+record.getProjectId())
				.addParam("manId", ""+manId)
				.addParam("womanId", ""+womanId);

			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			qr.update(sql.toSQLString());
		} catch (Exception exc) {
			exc.printStackTrace();
		}
		return id;
	}
	
	public void update(GedFamily record) {
		try {
			String projectId = (record.hasProjectId()) ? record.getProjectId() : "";

			String manId = (record.hasMan()) ? 
				((record.getMan().hasId()) ? record.getMan().getId() : "") : "";
			String womanId = (record.hasWoman()) ? 
				((record.getWoman().hasId()) ? record.getWoman().getId() : "") : "";
			
			UpdateQuery sql = new UpdateQuery(dbmd())
				.addFrom("family")
				.addParam("projectId", ""+projectId)
				.addParam("manId", ""+manId)
				.addParam("womanId", ""+womanId)
				//.addParam("descript", "")
			.add(new UniQLCondition("id", "=", "'"+record.getId()+"'"));
			
			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			qr.update(sql.toSQLString());
		} catch (Exception exc) {
			exc.printStackTrace();
		}
	}

	public List<GedPerson> selectChildern(GedFamily family) {
		//String fatherId = family.getMan().getId();
		//String matherId = family.getWoman().getId();
		//return selectChildern(fatherId, matherId);
		List<GedPerson> records =  new ArrayList<GedPerson>();
		try {
			/*
			String sql = 
			"SELECT * FROM person AS p " +
			"WHERE p.parents = '" + family.getId() + "' ";
			*/
			SelectQuery sql = new SelectQuery(dbmd())
				.addFrom("person AS p")
				.add(new UniQLCondition("p.parents", "=", "'" + family.getId() + "'"));
			
			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			List<Map<String, Object>> list = qr.query(sql.toSQLString(), new DefaultMapListHandler(new DefaultRowProcessor()));
			for (Map<String, Object> param : list) {				
				String id = (String) param.get("ID");
				GedPerson record = new GedPerson(id, param);
				records.add(record);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return records;
	}
	
	public List<GedPerson> selectChildern(String fatherId, String matherId) {
		List<GedPerson> records =  new ArrayList<GedPerson>();
		try {
			String sql = 
			"SELECT * FROM person AS p " +
			"WHERE p.FATHER_ID = '" + fatherId + "' " +
			"AND p.MATHER_ID = '" + matherId + "'";
			
			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			List<Map<String, Object>> list = qr.query(sql, new DefaultMapListHandler(new DefaultRowProcessor()));
			for (Map<String, Object> param : list) {				
				String id = (String) param.get("ID");
				GedPerson record = new GedPerson(id, param);
				records.add(record);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return records;
	}
 
	public List<GedFamily> selectByProjectId(String projectId) {
		List<GedFamily> records = new ArrayList<GedFamily>();
		try {
			SelectQuery sql = new SelectQuery(dbmd())
				.addFrom("family AS f")
				.add(new UniQLCondition("f.projectId", "=", "'" + projectId + "'"));
				
			QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
			List<Map<String, Object>> list = qr.query(sql.toSQLString(), new DefaultMapListHandler(new DefaultRowProcessor()));
			for (Map<String, Object> param : list) {				
				String id = (String) param.get("ID");
				GedFamily record = new GedFamily(id, param);
				records.add(record);
			}
		} catch (Exception exc) {
			exc.printStackTrace();
		}
		return records;
	}
	
	// -----  -----------------------------------------------------------------

	public boolean recordExist(String manId, String womanId) {
		try {
			UniQLWhere where = new UniQLWhere()
				.add(new UniQLCondition("family.ID", "=", "'"+manId+"'"), "AND")
				.add(new UniQLCondition("family.ID", "=", "'"+womanId+"'"));
			return super.existsRecord(where);
			
		} catch (Exception exc) {
			exc.printStackTrace();
		}
		return false;
	}

}
/*
public List<GedFamily> selectAll() {
	List<GedFamily> records =  new ArrayList<GedFamily>();
	try {
		SelectQuery sql = new SelectQuery().addFrom("family");
		QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
		List<Map<String, Object>> list = qr.query(sql.toSQLString(), new DefaultMapListHandler(new DefaultRowProcessor()));
		for (Map<String, Object> param : list) {				
			String id = (String) param.get("ID");
			GedFamily record = new GedFamily(id, param);
			records.add(record);
		}
	} catch (Exception exc) {
		exc.printStackTrace();
	}
	return records;
}

public GedFamily selectById(String id) {
	GedFamily record = null;
	try { 
	} catch (Exception exc) {
		exc.printStackTrace();
	}
	return record;
}
 
public String insert(GedFamily record) {
	String id = record.getId();
	try {
		if (id == null || id.trim().length() == 0 || !id.startsWith("GE.F.")) {
			id = UIDGenerator.createUID("GE.F");
			record.setId(id);
		}
		String manId = (record.hasMan()) ? 
			((record.getMan().hasId()) ? record.getMan().getId() : "") : "";
		String womanId = (record.hasWoman()) ? 
			((record.getWoman().hasId()) ? record.getWoman().getId() : "") : "";
			
		InsertQuery sql = new InsertQuery(dbmd()).into("family")
			.addParam("id", ""+record.getId())
			.addParam("projectId", ""+record.getProjectId())
			.addParam("manId", ""+manId)
			.addParam("womanId", ""+womanId)
			.addParam("descript", "");

		QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
		qr.update(sql.toSQLString());
	} catch (Exception exc) {
		exc.printStackTrace();
	}
	return id;
}
	
public void update(GedFamily record) {
	try {
		String projectId = (record.hasProjectId()) ? record.getProjectId() : "";

		String manId = (record.hasMan()) ? 
			((record.getMan().hasId()) ? record.getMan().getId() : "") : "";
		String womanId = (record.hasWoman()) ? 
			((record.getWoman().hasId()) ? record.getWoman().getId() : "") : "";
		
		UpdateQuery sql = new UpdateQuery(dbmd())
			.addFrom("family")
			.addParam("projectId", ""+projectId)
			.addParam("manId", ""+manId)
			.addParam("womanId", ""+womanId)
			//.addParam("descript", "")
		.add(new UniQLCondition("id", "=", "'"+record.getId()+"'"));
		
		QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
		qr.update(sql.toSQLString(true));
	} catch (Exception exc) {
		exc.printStackTrace();
	}
}
	
public List<GedPerson> selectChildern(GedFamily gedFamily) {
	String fatherId = gedFamily.getMan().getId();
	String matherId = gedFamily.getWoman().getId();		
	return selectChildern(fatherId, matherId);
}
	
public List<GedPerson> selectChildern(String fatherId, String matherId) {
	List<GedPerson> records =  new ArrayList<GedPerson>();
	try {
		String sql = 
		"SELECT * FROM person AS p " +
		"WHERE p.FATHER_ID = '" + fatherId + "' " +
		"AND p.MATHER_ID = '" + matherId + "'";
		
		QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
		List<Map<String, Object>> list = qr.query(sql, new DefaultMapListHandler(new DefaultRowProcessor()));
		for (Map<String, Object> param : list) {				
			String id = (String) param.get("ID");
			GedPerson record = new GedPerson(id, param);
			records.add(record);
		}
	} catch (Exception e) {
		e.printStackTrace();
	}
	return records;
}

public boolean recordExist(String id) {
	try {
		SelectQuery sql = new SelectQuery(DBConnector.instance().dbmd())
		.addFrom("family")
		.add(new UniQLCondition("family.ID", "=", "'"+id+"'"));
		QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
		List<Map<String, Object>> list = qr.query(sql.toSQLString(), new DefaultMapListHandler(new DefaultRowProcessor()));
		return !list.isEmpty();  
	} catch (Exception exc) {
		exc.printStackTrace();
	}
	return false;
}

public boolean recordExist(String manId, String womanId) {
	try {
		SelectQuery sql = new SelectQuery(DBConnector.instance().dbmd())
		.addFrom("family")
		.add(new UniQLCondition("family.ID", "=", "'"+manId+"'"), "AND")
		.add(new UniQLCondition("family.ID", "=", "'"+womanId+"'"));

		QueryRunner qr = new QueryRunner(DBConnector.instance().getDataSource());
		List<Map<String, Object>> list = qr.query(sql.toSQLString(), new DefaultMapListHandler(new DefaultRowProcessor()));
		return !list.isEmpty();  
	} catch (Exception exc) {
		exc.printStackTrace();
	}
	return false;
}
*/